create or replace package MaxTimeControl is

  -- Author  : Eric Guo
  -- Created : 5/9/2011 11:00:40 AM
  -- Purpose : Oracle DB package for Max Time Control Guard
  
  -- For SPAS SQ00317174
  -- Check if the time is less than 72 hours after last cure step move out to molding
  conLastCureBeforeMolding constant VARCHAR2(40) := 'LastCureBeforeMolding';
  
  -- For SPAS SQ00317466
  -- The time which previous step move out to the current monitor step Track-In time should less than 12 hours
  conPreviousMoveOutStep constant VARCHAR2(40) := 'PreviousMoveOutStep';
  
  -- For SPAS SQ00317466
  -- The time which previous step move out to the current monitor step Track-In time should less than 12 hours
  conBakingOutPackingCheck constant VARCHAR2(40) := 'BakingOutPackingCheck';

  -- Public function and procedure declarations
  FUNCTION CheckMaxTime(hmlID mtc_basic.recid%TYPE, checkType VARCHAR2) return VARCHAR2;

end MaxTimeControl;
/
create or replace package body MaxTimeControl is

  -- -- For SPAS SQ00317174, check if the time is less than 72 hours after last cure step move out to molding
  FUNCTION CheckMoldingMaxTime(hmlID        mtc_basic.recid%TYPE, checkType VARCHAR2,
             i_WorkOrder     mfgorder.mfgordername%TYPE,
             i_ProductName   historymainline.productname%TYPE,
             i_Lot           historymainline.containername%TYPE,
             i_TxnTime    historymainline.txndate%TYPE,
             i_CDOName       historymainline.cdoname%TYPE,
             i_CallByCDOName historymainline.callbycdoname%TYPE,
             i_SpecName      historymainline.specname%TYPE)
    RETURN VARCHAR2 IS
    Result mtc_basic.meslot%TYPE;
    v_CureName historymainline.specname%TYPE;

    CURSOR curHistoryMainLineCURE IS
    SELECT hml.txndate, hml.fromspecname
    FROM historymainline hml
    INNER JOIN spec ON hml.fromspecid=spec.specid
    WHERE hml.containername=i_Lot
    -- AND hml.txndate < to_date('5/11/2011 2:33:48 PM','mm/dd/yyyy hh:mi:ss AM')
     AND hml.txndate < i_TxnTime
     AND spec.description = 'CURE'
     AND hml.callbycdoname = 'LotMoveOut' AND hml.cdoname='MoveLot'
    ORDER BY hml.txndate DESC;
    
    v_CureTime historymainline.txndate%TYPE;
  BEGIN
    OPEN curHistoryMainLineCURE;
    LOOP
      EXIT WHEN curHistoryMainLineCURE%NOTFOUND;
      FETCH curHistoryMainLineCURE INTO v_CureTime, v_CureName;
      EXIT;
    END LOOP;
    CLOSE curHistoryMainLineCURE;
    
    IF (i_TxnTime - v_CureTime)*24 > 72  -- default is 72 hours
      AND ( i_Lot LIKE 'M%' OR i_Lot LIKE 'P%' OR i_Lot LIKE 'E%' OR i_Lot LIKE 'F%')
      THEN
      RESULT := i_Lot;
    END IF;
    
    IF RESULT IS NOT NULL THEN
      INSERT INTO MTC_BASIC
        (RECID, TxnName, OrderName, Product, MESLot, 
        END_STEP_Time, END_STEP_NAME, START_STEP_TIME, START_STEP_NAME)
      VALUES
        (hmlID, checkType, i_WorkOrder, i_ProductName, i_Lot,
        i_TxnTime, '3020', v_CureTime,v_CureName);
      COMMIT;
    END IF;    
    RETURN RESULT;
  END CheckMoldingMaxTime;

  FUNCTION CheckPreviousMoveOutStepTime(hmlID        mtc_basic.recid%TYPE, checkType VARCHAR2,
             i_WorkOrder     mfgorder.mfgordername%TYPE,
             i_ProductName   historymainline.productname%TYPE,
             i_Lot           historymainline.containername%TYPE,
             i_TxnTime    historymainline.txndate%TYPE,
             i_CDOName       historymainline.cdoname%TYPE,
             i_CallByCDOName historymainline.callbycdoname%TYPE,
             i_SpecName      historymainline.specname%TYPE)
    RETURN VARCHAR2 IS
    Result mtc_basic.meslot%TYPE := NULL;
    v_LastStepName historymainline.fromspecname%TYPE;
    v_LastStepTime historymainline.txndate%TYPE;

    CURSOR curHML_LastMoveOut IS
    SELECT hml.txndate, hml.Fromspecname
    FROM historymainline hml
    INNER JOIN spec ON hml.fromspecid=spec.specid
    WHERE hml.containername=i_Lot
    -- AND hml.txndate < to_date('5/11/2011 2:33:48 PM','mm/dd/yyyy hh:mi:ss AM')
     AND hml.txndate < i_TxnTime
     AND hml.callbycdoname = 'LotMoveOut' AND hml.cdoname='MoveLot'
    ORDER BY hml.txndate DESC;
    
  BEGIN
    OPEN curHML_LastMoveOut;
    LOOP
      EXIT WHEN curHML_LastMoveOut%NOTFOUND;
      FETCH curHML_LastMoveOut INTO v_LastStepTime, v_LastStepName;
      EXIT;
    END LOOP;
    CLOSE curHML_LastMoveOut;
    
    IF (i_TxnTime - v_LastStepTime)*24 > 12 -- default is 12 hours
      AND ( i_Lot LIKE 'M%'
      OR i_Lot LIKE 'P%'
      OR i_Lot LIKE 'E%'
      OR i_Lot LIKE 'F%')
      THEN 
      RESULT := i_Lot;
    END IF;
    
    IF RESULT IS NOT NULL THEN
      INSERT INTO MTC_BASIC
        (RECID, TxnName, OrderName, Product, MESLot, 
        START_STEP_TIME, START_STEP_NAME, END_STEP_TIME, END_STEP_NAME)
      VALUES
        (hmlID, checkType, i_WorkOrder, i_ProductName, i_Lot,
        v_LastStepTime,v_LastStepName, i_TxnTime, i_SpecName);
      COMMIT;
    END IF;
    RETURN RESULT;
  END CheckPreviousMoveOutStepTime;

   FUNCTION CheckBakingOutPackingTime(hmlID        mtc_basic.recid%TYPE, checkType VARCHAR2,
             i_WorkOrder     mfgorder.mfgordername%TYPE,
             i_ProductName   historymainline.productname%TYPE,
             i_Lot           historymainline.containername%TYPE,
             i_TxnTime    historymainline.txndate%TYPE,
             i_CDOName       historymainline.cdoname%TYPE,
             i_CallByCDOName historymainline.callbycdoname%TYPE,
             i_SpecName      historymainline.specname%TYPE)
    RETURN VARCHAR2 IS
    Result mtc_basic.meslot%TYPE;
    v_BackingName historymainline.specname%TYPE;

    CURSOR curHistoryMainLineBAKING IS
    SELECT hml.txndate, hml.fromspecname
    FROM historymainline hml
    WHERE hml.containername=i_Lot
    -- AND hml.txndate < to_date('5/11/2011 2:33:48 PM','mm/dd/yyyy hh:mi:ss AM')
     AND hml.txndate < i_TxnTime
     AND hml.specid='4803e08000005d63' -- 7055 BAKING step ID
     AND hml.callbycdoname = 'LotMoveOut' AND hml.cdoname='MoveLot'
    ORDER BY hml.txndate DESC;
    
    v_BackingTime historymainline.txndate%TYPE;
  BEGIN
    OPEN curHistoryMainLineBAKING;
    LOOP
      EXIT WHEN curHistoryMainLineBAKING%NOTFOUND;
      FETCH curHistoryMainLineBAKING INTO v_BackingTime, v_BackingName;
      EXIT;
    END LOOP;
    CLOSE curHistoryMainLineBAKING;
    
    IF (i_TxnTime - v_BackingTime)*24 > 11.5 -- default is 12 hours
      THEN
      RESULT := i_Lot;
    END IF;
    
    IF RESULT IS NOT NULL THEN
      INSERT INTO MTC_BASIC
        (RECID, TxnName, OrderName, Product, MESLot, 
        END_STEP_Time, END_STEP_NAME, START_STEP_TIME, START_STEP_NAME)
      VALUES
        (hmlID, checkType, i_WorkOrder, i_ProductName, i_Lot,
        i_TxnTime, i_SpecName, v_BackingTime,v_BackingName);
      COMMIT;
    END IF;    
    RETURN RESULT;
  END CheckBakingOutPackingTime;

  -- Function and procedure implementations
  FUNCTION CheckMaxTime(hmlID mtc_basic.recid%TYPE, checkType VARCHAR2)
    return VARCHAR2 is
    Result mtc_basic.meslot%TYPE;
    v_Lot           historymainline.containername%TYPE;
    v_TxnTime       historymainline.txndate%TYPE;
    v_WorkOrder     mfgorder.mfgordername%TYPE;
    v_ProductName   historymainline.productname%TYPE;
    v_SpecName      historymainline.specname%TYPE;
    v_CDOName       historymainline.cdoname%TYPE;
    v_CallByCDOName historymainline.callbycdoname%TYPE;
  BEGIN
    SELECT hml.containername, hml.txndate, mo.mfgordername, 
           hml.productname, hml.cdoname, hml.callbycdoname, hml.specname
    INTO v_Lot, v_TxnTime, v_WorkOrder, 
         v_ProductName, v_CDOName, v_CallByCDOName, v_SpecName
    FROM historymainline hml
    INNER JOIN container co ON co.containerid=hml.containerid
    LEFT JOIN mfgorder mo ON mo.mfgorderid=co.mfgorderid
    WHERE hml.historymainlineid=hmlID;
    
    IF checkType = conLastCureBeforeMolding THEN
      RESULT := CheckMoldingMaxTime(hmlID, checkType, v_WorkOrder, v_ProductName, 
      v_Lot, v_TxnTime, v_CDOName, v_CallByCDOName, v_SpecName);
    END IF;
    
    IF checkType = conPreviousMoveOutStep THEN
      RESULT := CheckPreviousMoveOutStepTime(hmlID, checkType, v_WorkOrder, v_ProductName, 
      v_Lot, v_TxnTime, v_CDOName, v_CallByCDOName, v_SpecName);
    END IF;
    
    IF checkType = conBakingOutPackingCheck THEN
      RESULT := CheckBakingOutPackingTime(hmlID, checkType, v_WorkOrder, v_ProductName, 
      v_Lot, v_TxnTime, v_CDOName, v_CallByCDOName, v_SpecName);
    END IF;
    
    RETURN RESULT;
  END CheckMaxTime;
  
END MaxTimeControl;
/
